package App.Repository;

import App.Model.*;
import App.Model.Chat.*;
import App.Model.Project.*;
import App.Model.Task.CrmTask;
import App.Model.Utility.GlobalSearchDetailListRequest;
import App.Model.Utility.GlobalSearchDetailListResponse;
import App.Model.Utility.GlobalSearchDetailRequest;
import App.Model.Utility.GlobalSearchDetailResponse;
import App.Repository.impl.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

//import sun.util.resources.cldr.te.CurrencyNames_te;


/**
 * Created by xw on 2018/11/30.
 */
@Repository
@Slf4j
public class ChatRepository extends BaseRepository {

    @Autowired
    CommonRepository _commonRepository;
    @Autowired
    private ProjectDao projectDao;
    @Autowired
    private ProjectTaskDao projectTaskDao;
    @Autowired
    private CrmChatGroupExtDao crmChatGroupExtDao;
    @Autowired
    private CrmTaskMemberDao crmTaskMemberDao;
    @Autowired
    private CrmProjectMemberDao crmProjectMemberDao;
    @Autowired
    private CrmProjectStageRelateDao crmProjectStageRelateDao;
    @Autowired
    private CrmProjectClientDao crmProjectClientDao;

    //聊天组列表v3
    public ChatGroupResponse getGroupListByPageV3(ChatGroupRequest request, int userid) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        ChatGroupResponse pageData = new ChatGroupResponse();
        //查询当前用户关联的项目或任务的聊天组id
        String strSql = "SELECT t1.groupid FROM crm_project_member t1 WHERE t1.recstatus = 1 AND t1.userid = :userid UNION\n" +
                "SELECT t2.groupid FROM crm_task_member t2 WHERE t2.recstatus = 1 AND t2.userid = :userid ";
        Map<String, Object> params = new HashMap<>();
        params.put("userid", userid);
        List<UserProjectTask> list = getEntitys(strSql, params, UserProjectTask.class);
        List<String> collects = list.stream().map(UserProjectTask::getGroupid).collect(Collectors.toList()).stream().distinct().collect(Collectors.toList());
        collects.removeAll(Collections.singleton(null));
        collects.removeAll(Collections.singleton(""));
        //获取当前用户的聊天组
        String strSql1 = "SELECT  recid, objectid, stageid  FROM crm_chat_group WHERE recstatus = 1 AND recid IN ( :groupids )";
        Map<String, Object> params1 = new HashMap<>();
        params1.put("groupids", collects);
        List<GroupObject> list1 = getEntitys(strSql1, params1, GroupObject.class);
        ArrayList<GroupObject> collect = list1.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(o -> o.getObjectid() + ";" + o.getStageid()))), ArrayList::new));
        List<String> collects1 = collect.stream().map(GroupObject::getRecid).collect(Collectors.toList());
        List<StageGroup> list2 = getStageGroupList(request, collects1);
        List<ChatGroupListResponse> results = new ArrayList<>();
        for (StageGroup stageGroup : list2) {
            ChatGroupListResponse response = new ChatGroupListResponse();
            String projectid = stageGroup.getObjectid();
            int stageid = stageGroup.getStageid();
            String projectname = "";
            String stagename = "";
            String completedtime = "";
            boolean inlawyermember = true;
            boolean iscomplete;
            List<ChatGroupListItem> groups = new ArrayList<>();
            //项目
            if (stageGroup.getChattype() == 0) {
                //项目信息
                CrmProject project = getProject(projectid);
                projectname = project.getRecname();
                //阶段信息
                CrmProjectStage stage = getProjectStage(stageid);
                stagename = stage.getStagename();
                CrmProjectStageRelate stageRelate = getProjectStageRelate(projectid, stageid);
                completedtime = dateFormat.format(stageRelate.getCompletedtime());
                if (stageRelate.getStagestatus() == 2) {
                    iscomplete = true;
                } else {
                    iscomplete = false;
                }
                //当用用户是否律师协作团队
                inlawyermember = false;
                Set<String> projectIds = new HashSet<>();
                projectIds.add(projectid);
                List<CrmProjectMember> members = crmProjectMemberDao.findByProjectId(projectIds);
                if (members != null && members.size() > 0) {
                    int finalStageid = stageid;
                    long laywerMemberCount = members.stream().filter(x -> x.getStageid() == finalStageid
                            && x.getGrouptype() == BaseEnum.GroupType.Lawyer.getGroupType()
                            && x.getUserid() == userid).count();
                    if (laywerMemberCount > 0) {
                        inlawyermember = true;
                    }
                }
                List<CrmProjectClient> client = crmProjectClientDao.getClientByProjectId(projectid);
                //获取委托方信息
                String titleid = "";
                if (client != null) {
                    titleid = client.get(0).getTitleid();
                }
                //聊天组信息
                List<CrmChatGroup> projectGroups = getCrmGroup(stageGroup);
                List<Date> lastTimes = new ArrayList<>();
                for (CrmChatGroup group : projectGroups) {
                    //不是律师协作团队,只查询委托聊天组
                    if (!inlawyermember && group.getGrouptype() != 1) {
                        continue;
                    }
                    ChatGroupListItem item = new ChatGroupListItem();
                    item.setGroupid(group.getGroupid());
                    item.setGroupname(group.getGroupname());
                    item.setGrouptype(group.getGrouptype());
                    item.setChattype(group.getChattype());
                    item.setProjecttype(project.getProjecttype());
                    item.setObjectid(group.getObjectid());
                    item.setStageid(group.getProjectstageid());
                    item.setProjectid(group.getObjectid());
                    item.setIsavtive(group.getIsactive());
                    item.setCurrentchatstageid(group.getStageid());
                    item.setIscomplete(iscomplete);
                    //聊天阶段没有推进
                    String currentChatStageName = "委协";
                    if (item.getCurrentchatstageid() > 0) {
                        List<CrmChatStage> theChatStage = getChatStages_new(item.getCurrentchatstageid());
                        if (theChatStage != null && theChatStage.size() > 0) {
                            //阶段名称
                            currentChatStageName = theChatStage.get(0).getStagename();
                        }
                    } else {
                        if (titleid != null && !titleid.isEmpty()) {
                            List<CrmChatStage> chatStages = getChatStages(project.getProjecttype(), group.getGroupid(), group.getProjectstageid(), titleid);
                            if (group.getStageid() == 0) {
                                if (chatStages != null && chatStages.size() > 0) {
                                    CrmChatStage currentChatStage = chatStages.get(0);
                                    item.setCurrentchatstageid(currentChatStage.getStageid());
                                }
                            }
                            //设置聊天阶段名字
                            Optional<CrmChatStage> theChatStage = chatStages.stream().filter(x -> x.getStageid() == item.getCurrentchatstageid()).findFirst();
                            if (theChatStage.isPresent()) {
                                if (!theChatStage.get().getStagename().isEmpty()) {
                                    currentChatStageName = theChatStage.get().getStagename();
                                }
                            } else {
                                ChatStageGetRequest _chatStageRequest = new ChatStageGetRequest();
                                _chatStageRequest.setGroupid(item.getGroupid());
                                _chatStageRequest.setStageid(item.getStageid());

                                List<ChatStage> _theChatStages = getChatStage(_chatStageRequest, userid);
                                if (_theChatStages != null && _theChatStages.size() > 0) {

                                    if (_theChatStages.get(0).getStagename() != null) {
                                        currentChatStageName = _theChatStages.get(0).getStagename();
                                    }
                                }
                            }
                        } else {
                            if (item.getGrouptype() == 3) {
                                currentChatStageName = "律协";
                            }
                        }
                    }
                    item.setCurrentchatstagename(currentChatStageName);
                    getMsgInfo(item, group.getGroupid());
                    if(item.getLatestchattime()==null||StringUtils.isEmpty(item.getLatestchattime())){
                        if(group.getCreatetime()!=null){
                            item.setLatestchattime(dateFormat.format(group.getCreatetime()));
                        }
                    }
                    //阶段未完成获取最后的聊天内容的时间
                    if (!iscomplete && StringUtils.hasText(item.getLatestchattime())) {
                        try {
                            lastTimes.add(dateFormat.parse(item.getLatestchattime()));
                        } catch (ParseException e) {
                            log.error("时间转换错误!!" + item.getLatestchattime());
                            e.printStackTrace();
                        }
                    }


                    groups.add(item);
                }
                if (lastTimes.size() > 0) {
                    completedtime = dateFormat.format(Collections.max(lastTimes));
                }
            }
            //任务
            if (stageGroup.getChattype() == 1) {
                //任务信息
                CrmTask task = getTask(projectid);
                completedtime = dateFormat.format(task.getCompletetime());
                if (task.getTaskstatus() == BaseEnum.TaskStatus.Complete.getTaskStatus()) {
                    iscomplete = true;
                } else {
                    iscomplete = false;
                }
                //聊天组
                List<CrmChatGroup> taskGroups = getCrmGroup(stageGroup);
                List<Date> lastTimes = new ArrayList<>();
                for (CrmChatGroup group : taskGroups) {
                    stageid = group.getStageid();
                    ChatGroupListItem item = new ChatGroupListItem();
                    item.setGroupname(task.getContent());
                    item.setChattype(group.getChattype());
                    item.setBegintime(dateFormat.format(task.getBegintime()));
                    item.setGroupid(group.getGroupid());
                    item.setGrouptype(group.getGrouptype());
                    item.setProjecttype(0);
                    item.setObjectid(group.getObjectid());
                    item.setStageid(group.getProjectstageid());
                    item.setProjectid(group.getObjectid());
                    item.setIsavtive(group.getIsactive());
                    item.setCurrentchatstageid(group.getStageid());
                    item.setIscomplete(iscomplete);
                    item.setCurrentchatstagename("委托");
                    getMsgInfo(item, group.getGroupid());
                    if(item.getLatestchattime()==null||StringUtils.isEmpty(item.getLatestchattime())){
                        if(group.getCreatetime()!=null){
                            item.setLatestchattime(dateFormat.format(group.getCreatetime()));
                        }
                    }
                    if (!iscomplete && StringUtils.hasText(item.getLatestchattime())) {
                        try {
                            lastTimes.add(dateFormat.parse(item.getLatestchattime()));
                        } catch (ParseException e) {
                            log.error("时间转换错误!!" + item.getLatestchattime());
                            e.printStackTrace();
                        }
                    }
                    groups.add(item);
                }
                if (lastTimes.size() > 0) {
                    completedtime = dateFormat.format(Collections.max(lastTimes));
                }
            }
            response.setProjectid(projectid);
            response.setProjectname(projectname);
            response.setStageid(stageid);
            response.setStagename(stagename);
            response.setCompletedtime(completedtime);
            response.setInlawyermember(inlawyermember);
            response.setGroups(groups);
            results.add(response);
        }
        List<ChatGroupListResponse> noTimeList = results.stream().filter(c -> c.getCompletedtime() == null).collect(Collectors.toList());
        List<ChatGroupListResponse> completeList = results.stream().filter(c -> c.getCompletedtime() != null).sorted(Comparator.comparing(ChatGroupListResponse::getCompletedtime, Comparator.reverseOrder())).collect(Collectors.toList());
        completeList.addAll(noTimeList);
        pageData.setDatalist(completeList);
        return pageData;
    }

    //聊天组列表v2
    public ChatGroupResponse getGroupListByPageV2(ChatGroupRequest request, int userid) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        ChatGroupResponse pageData = new ChatGroupResponse();
        //查询当前用户关联的项目或任务的聊天组id
        String strSql = "SELECT t1.groupid FROM crm_project_member t1 WHERE t1.recstatus = 1 AND t1.userid = :userid UNION\n" +
                "SELECT t2.groupid FROM crm_task_member t2 WHERE t2.recstatus = 1 AND t2.userid = :userid ";
        Map<String, Object> params = new HashMap<>();
        params.put("userid", userid);
        List<UserProjectTask> list = getEntitys(strSql, params, UserProjectTask.class);
        List<String> collects = list.stream().map(UserProjectTask::getGroupid).collect(Collectors.toList()).stream().distinct().collect(Collectors.toList());
        collects.removeAll(Collections.singleton(null));
        collects.removeAll(Collections.singleton(""));
        //获取当前用户的聊天组
        String strSql1 = "SELECT  recid, objectid, stageid  FROM crm_chat_group WHERE recstatus = 1 AND recid IN ( :groupids )";
        Map<String, Object> params1 = new HashMap<>();
        params1.put("groupids", collects);
        List<GroupObject> list1 = getEntitys(strSql1, params1, GroupObject.class);
        ArrayList<GroupObject> collect = list1.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(o -> o.getObjectid() + ";" + o.getStageid()))), ArrayList::new));
        List<String> collects1 = collect.stream().map(GroupObject::getRecid).collect(Collectors.toList());
        List<StageGroup> list2 = getStageGroupList(request, collects1);
        List<ChatGroupListResponse> results = new ArrayList<>();
        for (StageGroup stageGroup : list2) {
            ChatGroupListResponse response = new ChatGroupListResponse();
            String projectid = stageGroup.getObjectid();
            int stageid = stageGroup.getStageid();
            String projectname = "";
            String stagename = "";
            String completedtime = "";
            boolean inlawyermember = true;
            boolean iscomplete;
            List<ChatGroupListItem> groups = new ArrayList<>();
            //项目
            if (stageGroup.getChattype() == 0) {
                //项目信息
                CrmProject project = getProject(projectid);
                projectname = project.getRecname();
                //阶段信息
                CrmProjectStage stage = getProjectStage(stageid);
                stagename = stage.getStagename();
                CrmProjectStageRelate stageRelate = getProjectStageRelate(projectid, stageid);
                completedtime = dateFormat.format(stageRelate.getCompletedtime());
                if (stageRelate.getStagestatus() == 2) {
                    iscomplete = true;
                } else {
                    iscomplete = false;
                }
                //当用用户是否律师协作团队
                inlawyermember = false;
                Set<String> projectIds = new HashSet<>();
                projectIds.add(projectid);
                List<CrmProjectMember> members = crmProjectMemberDao.findByProjectId(projectIds);
                if (members != null && members.size() > 0) {
                    int finalStageid = stageid;
                    long laywerMemberCount = members.stream().filter(x -> x.getStageid() == finalStageid
                            && x.getGrouptype() == BaseEnum.GroupType.Lawyer.getGroupType()
                            && x.getUserid() == userid).count();
                    if (laywerMemberCount > 0) {
                        inlawyermember = true;
                    }
                }
                List<CrmProjectClient> client = crmProjectClientDao.getClientByProjectId(projectid);
                //获取委托方信息
                String titleid = "";
                if (client != null) {
                    titleid = client.get(0).getTitleid();
                }
                //聊天组信息
                List<CrmChatGroup> projectGroups = getCrmGroup(stageGroup);
                List<Date> lastTimes = new ArrayList<>();
                for (CrmChatGroup group : projectGroups) {
                    //不是律师协作团队,只查询委托聊天组
                    if (!inlawyermember && group.getGrouptype() != 1) {
                        continue;
                    }
                    // FIX：1. 账号现在只是加入了“律师协作团队"，不应该显示"委协"的群聊，但是现在显示了出来
                    // collects是用户聊天组,去掉一些不是该用户的聊天组
                    if (!collects.contains(group.getGroupid())){
                        continue;
                    }
                    ChatGroupListItem item = new ChatGroupListItem();
                    item.setGroupid(group.getGroupid());
                    item.setGroupname(group.getGroupname());
                    item.setGrouptype(group.getGrouptype());
                    item.setChattype(group.getChattype());
                    item.setProjecttype(project.getProjecttype());
                    item.setObjectid(group.getObjectid());
                    item.setStageid(group.getProjectstageid());
                    item.setProjectid(group.getObjectid());
                    item.setIsavtive(group.getIsactive());
                    item.setCurrentchatstageid(group.getStageid());
                    item.setIscomplete(iscomplete);
                    item.setColor(group.getColor());
                    //聊天阶段没有推进
                    String currentChatStageName = "委协";
                    if (item.getCurrentchatstageid() > 0) {
                        List<CrmChatStage> theChatStage = getChatStages_new(item.getCurrentchatstageid());
                        if (theChatStage != null && theChatStage.size() > 0) {
                            //阶段名称
                            currentChatStageName = theChatStage.get(0).getStagename();
                        }
                    } else {
                        if (titleid != null && !titleid.isEmpty()) {
                            List<CrmChatStage> chatStages = getChatStages(project.getProjecttype(), group.getGroupid(), group.getProjectstageid(), titleid);
                            if (group.getStageid() == 0) {
                                if (chatStages != null && chatStages.size() > 0) {
                                    CrmChatStage currentChatStage = chatStages.get(0);
                                    item.setCurrentchatstageid(currentChatStage.getStageid());
                                }
                            }
                            //设置聊天阶段名字
                            Optional<CrmChatStage> theChatStage = chatStages.stream().filter(x -> x.getStageid() == item.getCurrentchatstageid()).findFirst();
                            if (theChatStage.isPresent()) {
                                if (!theChatStage.get().getStagename().isEmpty()) {
                                    currentChatStageName = theChatStage.get().getStagename();
                                }
                            } else {
                                ChatStageGetRequest _chatStageRequest = new ChatStageGetRequest();
                                _chatStageRequest.setGroupid(item.getGroupid());
                                _chatStageRequest.setStageid(item.getStageid());

                                List<ChatStage> _theChatStages = getChatStage(_chatStageRequest, userid);
                                if (_theChatStages != null && _theChatStages.size() > 0) {

                                    if (_theChatStages.get(0).getStagename() != null) {
                                        currentChatStageName = _theChatStages.get(0).getStagename();
                                    }
                                }
                            }
                        } else {
                            if (item.getGrouptype() == 3) {
                                currentChatStageName = "律协";
                            }
                        }
                    }
                    item.setCurrentchatstagename(currentChatStageName);
                    getMsgInfo(item, group.getGroupid());
                        if(item.getLatestchattime()==null||StringUtils.isEmpty(item.getLatestchattime())){
                            if(group.getCreatetime()!=null){
                            item.setLatestchattime(dateFormat.format(group.getCreatetime()));
                        }
                    }
                    //阶段未完成获取最后的聊天内容的时间
                    if (!iscomplete && StringUtils.hasText(item.getLatestchattime())) {
                        try {
                            lastTimes.add(dateFormat.parse(item.getLatestchattime()));
                        } catch (ParseException e) {
                            log.error("时间转换错误!!" + item.getLatestchattime());
                            e.printStackTrace();
                        }
                    }


                    groups.add(item);
                }
                if (lastTimes.size() > 0) {
                    completedtime = dateFormat.format(Collections.max(lastTimes));
                }
            }
            //任务
            if (stageGroup.getChattype() == 1) {
                //任务信息
                CrmTask task = getTask(projectid);
                completedtime = dateFormat.format(task.getCompletetime());
                if (task.getTaskstatus() == BaseEnum.TaskStatus.Complete.getTaskStatus()) {
                    iscomplete = true;
                } else {
                    iscomplete = false;
                }
                //聊天组
                List<CrmChatGroup> taskGroups = getCrmGroup(stageGroup);
                List<Date> lastTimes = new ArrayList<>();
                for (CrmChatGroup group : taskGroups) {
                    stageid = group.getStageid();
                    ChatGroupListItem item = new ChatGroupListItem();
                    item.setGroupname(task.getContent());
                    item.setChattype(group.getChattype());
                    item.setBegintime(dateFormat.format(task.getBegintime()));
                    item.setGroupid(group.getGroupid());
                    item.setGrouptype(group.getGrouptype());
                    item.setProjecttype(0);
                    item.setObjectid(group.getObjectid());
                    item.setStageid(group.getProjectstageid());
                    item.setProjectid(group.getObjectid());
                    item.setIsavtive(group.getIsactive());
                    item.setCurrentchatstageid(group.getStageid());
                    item.setIscomplete(iscomplete);
                    item.setCurrentchatstagename("委托");
                    item.setColor(group.getColor());
                    getMsgInfo(item, group.getGroupid());
                    if(item.getLatestchattime()==null||StringUtils.isEmpty(item.getLatestchattime())){
                        if(group.getCreatetime()!=null){
                            item.setLatestchattime(dateFormat.format(group.getCreatetime()));
                        }
                    }
                    if (!iscomplete && StringUtils.hasText(item.getLatestchattime())) {
                        try {
                            lastTimes.add(dateFormat.parse(item.getLatestchattime()));
                        } catch (ParseException e) {
                            log.error("时间转换错误!!" + item.getLatestchattime());
                            e.printStackTrace();
                        }
                    }
                    groups.add(item);
                }
                if (lastTimes.size() > 0) {
                    completedtime = dateFormat.format(Collections.max(lastTimes));
                }
            }
            response.setProjectid(projectid);
            response.setProjectname(projectname);
            response.setStageid(stageid);
            response.setStagename(stagename);
            response.setCompletedtime(completedtime);
            response.setInlawyermember(inlawyermember);
            response.setGroups(groups);
            results.add(response);
        }
        List<ChatGroupListResponse> noTimeList = results.stream().filter(c -> c.getCompletedtime() == null).collect(Collectors.toList());
        List<ChatGroupListResponse> completeList = results.stream().filter(c -> c.getCompletedtime() != null).sorted(Comparator.comparing(ChatGroupListResponse::getCompletedtime, Comparator.reverseOrder())).collect(Collectors.toList());
        completeList.addAll(noTimeList);
        pageData.setDatalist(completeList);
        return pageData;
    }

    public ChatGroupListResponse getStageGroup(String groupid, int userid) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        ChatGroupListResponse response = new ChatGroupListResponse();
        List<String> groupids = new ArrayList<>();
        groupids.add(groupid);
        List<StageGroup> list = getStageGroupList(new ChatGroupRequest(), groupids);
        StageGroup stageGroup = list.get(0);

        String projectid = stageGroup.getObjectid();
        int stageid = stageGroup.getStageid();
        String projectname = "";
        String stagename = "";
        String completedtime = "";
        boolean inlawyermember = true;
        boolean iscomplete;
        List<ChatGroupListItem> groups = new ArrayList<>();
        //项目
        if (stageGroup.getChattype() == 0) {
            //项目信息
            CrmProject project = getProject(projectid);
            projectname = project.getRecname();
            //阶段信息
            CrmProjectStage stage = getProjectStage(stageid);
            stagename = stage.getStagename();
            CrmProjectStageRelate stageRelate = getProjectStageRelate(projectid, stageid);
            completedtime = dateFormat.format(stageRelate.getCompletedtime());
            if (stageRelate.getStagestatus() == 2) {
                iscomplete = true;
            } else {
                iscomplete = false;
            }
            //当用用户是否律师协作团队
            inlawyermember = false;
            Set<String> projectIds = new HashSet<>();
            projectIds.add(projectid);
            List<CrmProjectMember> members = crmProjectMemberDao.findByProjectId(projectIds);
            if (members != null && members.size() > 0) {
                int finalStageid = stageid;
                long laywerMemberCount = members.stream().filter(x -> x.getStageid() == finalStageid
                        && x.getGrouptype() == BaseEnum.GroupType.Lawyer.getGroupType()
                        && x.getUserid() == userid).count();
                if (laywerMemberCount > 0) {
                    inlawyermember = true;
                }
            }
            List<CrmProjectClient> client = crmProjectClientDao.getClientByProjectId(projectid);
            //获取委托方信息
            String titleid = "";
            if (client != null) {
                titleid = client.get(0).getTitleid();
            }
            //聊天组信息
            List<CrmChatGroup> projectGroups = getCrmGroup(stageGroup);
            List<Date> lastTimes = new ArrayList<>();
            for (CrmChatGroup group : projectGroups) {
                //不是律师协作团队,只查询委托聊天组
                if (!inlawyermember && group.getGrouptype() != 1) {
                    continue;
                }
                ChatGroupListItem item = new ChatGroupListItem();
                item.setGroupid(group.getGroupid());
                item.setGroupname(group.getGroupname());
                item.setGrouptype(group.getGrouptype());
                item.setChattype(group.getChattype());
                item.setProjecttype(project.getProjecttype());
                item.setObjectid(group.getObjectid());
                item.setStageid(group.getProjectstageid());
                item.setProjectid(group.getObjectid());
                item.setIsavtive(group.getIsactive());
                item.setCurrentchatstageid(group.getStageid());
                item.setIscomplete(iscomplete);
                //聊天阶段没有推进
                String currentChatStageName = "委协";
                if (item.getCurrentchatstageid() > 0) {
                    List<CrmChatStage> theChatStage = getChatStages_new(item.getCurrentchatstageid());
                    if (theChatStage != null && theChatStage.size() > 0) {
                        //阶段名称
                        currentChatStageName = theChatStage.get(0).getStagename();
                    }
                } else {
                    if (titleid != null && !titleid.isEmpty()) {
                        List<CrmChatStage> chatStages = getChatStages(project.getProjecttype(), group.getGroupid(), group.getProjectstageid(), titleid);
                        if (group.getStageid() == 0) {
                            if (chatStages != null && chatStages.size() > 0) {
                                CrmChatStage currentChatStage = chatStages.get(0);
                                item.setCurrentchatstageid(currentChatStage.getStageid());
                            }
                        }
                        //设置聊天阶段名字
                        Optional<CrmChatStage> theChatStage = chatStages.stream().filter(x -> x.getStageid() == item.getCurrentchatstageid()).findFirst();
                        if (theChatStage.isPresent()) {
                            if (!theChatStage.get().getStagename().isEmpty()) {
                                currentChatStageName = theChatStage.get().getStagename();
                            }
                        } else {
                            ChatStageGetRequest _chatStageRequest = new ChatStageGetRequest();
                            _chatStageRequest.setGroupid(item.getGroupid());
                            _chatStageRequest.setStageid(item.getStageid());

                            List<ChatStage> _theChatStages = getChatStage(_chatStageRequest, userid);
                            if (_theChatStages != null && _theChatStages.size() > 0) {

                                if (_theChatStages.get(0).getStagename() != null) {
                                    currentChatStageName = _theChatStages.get(0).getStagename();
                                }
                            }
                        }
                    } else {
                        if (item.getGrouptype() == 3) {
                            currentChatStageName = "律协";
                        }
                    }
                }
                item.setCurrentchatstagename(currentChatStageName);
                getMsgInfo(item, group.getGroupid());
                //阶段未完成获取最后的聊天内容的时间
                if (!iscomplete && StringUtils.hasText(item.getLatestchattime())) {
                    try {
                        lastTimes.add(dateFormat.parse(item.getLatestchattime()));
                    } catch (ParseException e) {
                        log.error("时间转换错误!!" + item.getLatestchattime());
                        e.printStackTrace();
                    }
                }
                groups.add(item);
            }
            if (lastTimes.size() > 0) {
                completedtime = dateFormat.format(Collections.max(lastTimes));
            }
        }
        //任务
        if (stageGroup.getChattype() == 1) {
            //任务信息
            CrmTask task = getTask(projectid);
            completedtime = dateFormat.format(task.getCompletetime());
            if (task.getTaskstatus() == BaseEnum.TaskStatus.Complete.getTaskStatus()) {
                iscomplete = true;
            } else {
                iscomplete = false;
            }
            //聊天组
            List<CrmChatGroup> taskGroups = getCrmGroup(stageGroup);
            List<Date> lastTimes = new ArrayList<>();
            for (CrmChatGroup group : taskGroups) {
                stageid = group.getStageid();
                ChatGroupListItem item = new ChatGroupListItem();
                item.setGroupname(task.getContent());
                item.setChattype(group.getChattype());
                item.setBegintime(dateFormat.format(task.getBegintime()));
                item.setGroupid(group.getGroupid());
                item.setGrouptype(group.getGrouptype());
                item.setProjecttype(0);
                item.setObjectid(group.getObjectid());
                item.setStageid(group.getProjectstageid());
                item.setProjectid(group.getObjectid());
                item.setIsavtive(group.getIsactive());
                item.setCurrentchatstageid(group.getStageid());
                item.setIscomplete(iscomplete);
                item.setCurrentchatstagename("委托");
                getMsgInfo(item, group.getGroupid());
                if (!iscomplete && StringUtils.hasText(item.getLatestchattime())) {
                    try {
                        lastTimes.add(dateFormat.parse(item.getLatestchattime()));
                    } catch (ParseException e) {
                        log.error("时间转换错误!!" + item.getLatestchattime());
                        e.printStackTrace();
                    }
                }
                groups.add(item);
            }
            if (lastTimes.size() > 0) {
                completedtime = dateFormat.format(Collections.max(lastTimes));
            }
        }
        response.setProjectid(projectid);
        response.setProjectname(projectname);
        response.setStageid(stageid);
        response.setStagename(stagename);
        response.setCompletedtime(completedtime);
        response.setInlawyermember(inlawyermember);
        response.setGroups(groups);
        return response;
    }


    private List<StageGroup> getStageGroupList(ChatGroupRequest request, List<String> groupids) {
        String strSql2 = "SELECT recid, stageid, objectid, chattype, createtime FROM crm_chat_group \n" +
                "WHERE recid IN ( :groupids ) ";
        String suffix = " ORDER BY createtime DESC ";
        if (request.getPageindex() > 0) {
            int _pageindex = request.getPageindex();
            int _pageSize = request.getPagesize();
            int _offset = (_pageindex - 1) * _pageSize;
            suffix = " ORDER BY createtime DESC LIMIT  " + _pageSize + " OFFSET " + _offset;
        }
        Map<String, Object> params2 = new HashMap<>();
        params2.put("groupids", groupids);
        return getEntitys(strSql2 + suffix, params2, StageGroup.class);
    }

    private CrmProjectStageRelate getProjectStageRelate(String projectid, int stageid) {
        String stageSql = "SELECT recid,stageid,projectid,stagestatus,completedtime,createtime FROM crm_project_stage_relate " +
                "WHERE recstatus = 1 AND projectid = :projectid AND stageid = :stageid ";
        Map<String, Object> params = new HashMap<>();
        params.put("projectid", projectid);
        params.put("stageid", stageid);
        List<CrmProjectStageRelate> list = getEntitys(stageSql, params, CrmProjectStageRelate.class);
        return list.get(0);
    }

    private List<CrmChatGroup> getCrmGroup(StageGroup stageGroup) {
        //根据项目id和阶段id查出聊天组
        String groupSql = "SELECT color,recid,recname,objectid,chattype,chatstageid,prefixname,stageid,grouptype,relatedid,''as taskbegintime,isactive,createtime FROM crm_chat_group " +
                "WHERE recstatus = 1 AND objectid = :objectid AND stageid = :stageid AND chattype = :chattype ";
        Map<String, Object> params = new HashMap<>();
        params.put("objectid", stageGroup.getObjectid());
        params.put("stageid", stageGroup.getStageid());
        params.put("chattype", stageGroup.getChattype());
        return getEntitys(groupSql, params, CrmChatGroup.class);
    }

    public List<CrmChatGroup> getCrmGroupByStatus() {
        //根据项目id和阶段id查出聊天组
        String groupSql = "SELECT color,recid,recname,objectid,chattype,chatstageid,prefixname,stageid,grouptype,relatedid,''as taskbegintime,isactive,createtime FROM crm_chat_group " +
                "WHERE recstatus = :recstatus AND isactive = :isactive ";
        Map<String, Object> params = new HashMap<>();
        params.put("recstatus", 1);
        params.put("isactive", 0);
        return getEntitys(groupSql, params, CrmChatGroup.class);
    }

    private void getMsgInfo(ChatGroupListItem item, String groupid) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String content = "";
        String msgcontent = "";
        String dataStr = "";
        String sender = "";
        long msgtime = 0;
        int msgseq = 0;
        Map<String, Object> params = new HashMap<>();
        params.put("groupid", groupid);
//        String contentSql = "select groupid,content,msgtime,msgseq,msgcontent,createtime from crm_chat_callback_message as tmp_call " +
//                "where tmp_call.groupid = :groupid order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1";
        String contentSql = "select groupid,content,msgtime,msgseq,msgcontent,createtime from crm_chat_callback_message as tmp_call " +
                "where tmp_call.groupid = :groupid order by tmp_call.msgtime desc,tmp_call.msgseq desc";
        List<CrmChatCallbackMessage> list = getEntitys(contentSql, params, CrmChatCallbackMessage.class);
        if (list.size() > 0) {
            content = list.get(0).getContent();
            msgcontent = list.get(0).getMsgcontent();
            msgtime = list.get(0).getMsgtime();
            msgseq = list.get(0).getMsgseq();
            dataStr = dateFormat.format(list.get(0).getCreatetime());
        }
        item.setContent(content);
        item.setMsgcontent(msgcontent);
        item.setMsgtime(msgtime);
        item.setMsgseq(msgseq);
        item.setLatestchattime(dataStr);
//        String countSql = " select groupid,content,msgtime,msgseq,msgcontent,createtime from crm_chat_callback_message as tmp_call where tmp_call.groupid = :groupid ";
//        List<CrmChatCallbackMessage> counts = getEntitys(countSql, params, CrmChatCallbackMessage.class);
//        item.setRelcount(counts.size());
        item.setRelcount(list.size());
    }


    //聊天组列表
    public ChatGroupResponse getGroupListByPage(ChatGroupRequest request, int userid) {
        ChatGroupResponse pageData = new ChatGroupResponse();
        List<ChatGroup> _final = new ArrayList<>();
        List<ChatGroupListResponse> _listResponse = new ArrayList<>();

        SimpleDateFormat _dataFormat24Hour = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        try {
            List<String> groupIds = crmTaskMemberDao.getGroupIdsByUserId(userid);
            groupIds.addAll(crmProjectMemberDao.getGroupIdsByUserId(userid));
            //-----获取群里详情使用---------
            if (!StringUtils.isEmpty(request.getGroupId())) {
                groupIds.clear();
                groupIds.add(request.getGroupId());
            }
            //-----获取群里详情使用---------

            groupIds.removeAll(Collections.singleton(""));
            String _strCountSql = "";

            String strSql =
                    "select recid,recname,objectid,chattype,ifnull(chatstageid,0) as chatstageid ,prefixname,stageid,grouptype,relatedid,'' as taskbegintime,isactive, " +
                            " (select count(1) from crm_chat_callback_message as tmp_call where  tmp_call.groupid = g.recid) as relcount, " +
                            " (select content from crm_chat_callback_message as tmp_call where tmp_call.groupid = g.recid order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1 ) as content, " +
                            "  (select COALESCE((select msgtime from crm_chat_callback_message as tmp_call where tmp_call.groupid = g.recid " +
                            "  order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1), 0))  as msgtime, " +
                            " (select COALESCE((select msgseq from crm_chat_callback_message as tmp_call where tmp_call.groupid = g.recid" +
                            "  order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1), 0))  as msgseq, " +
                            " (select msgcontent from crm_chat_callback_message as tmp_call where tmp_call.groupid = g.recid order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1 ) as msgcontent " +
                            " from crm_chat_group g " +
                            " where recstatus=1 and recid in (:groupid) ";
            String suffix = "  order by createtime desc ";
            if (request.getPageindex() > 0) {
                int _pageindex = request.getPageindex();
                int _pageSize = request.getPagesize();
                int _offset = (_pageindex - 1) * _pageSize;

                _strCountSql = String.format(" select count(1) from (%s) as t  ", strSql);
                suffix = "  order by createtime desc limit " + _pageSize + " offset " + _offset;
            }
            Map<String, Object> _params = new HashMap<>();
            _params.put("groupid", groupIds);
            List<CrmChatGroupExt> _list = getEntitys(strSql + suffix, _params, CrmChatGroupExt.class);
            if (_list != null && _list.size() > 0 && !_strCountSql.isEmpty()) {
                int _count = crmChatGroupExtDao.findCountByGroupId(groupIds);
                PageCountInfo page = new PageCountInfo();
                page.setPage(request.getPageindex());
                page.setTotal(_count);
                pageData.setPage(page);
            }
            //获取项目聊天组:chatType = 0
            List<CrmChatGroupExt> _projectGroups = _list.stream().filter(x -> x.getObjectid() != null
                    && !x.getObjectid().isEmpty()
                    && x.getChattype() == BaseEnum.ChatType.Project.getChatType()).collect(Collectors.toList());
            //获取任务聊天组:chatType = 1
            List<CrmChatGroupExt> _taskGroups = _list.stream().filter(x -> x.getObjectid() != null
                    && !x.getObjectid().isEmpty()
                    && x.getChattype() == BaseEnum.ChatType.Task.getChatType()).collect(Collectors.toList());


            //获取项目id
            boolean _isComplete = false;
            Set<String> _projectIds = _projectGroups.stream().map(CrmChatGroupExt::getObjectid).collect(Collectors.toSet());
            //获取项目下所有可用任务
            List<CrmProjectTask> _projectTask = projectTaskDao.getProjectTask(_projectIds);
            //获取项目基本信息
            List<CrmProject> projects = projectDao.getProjectByIds(_projectIds);
            //获取项目下所有团队成员
            List<CrmProjectMember> members = crmProjectMemberDao.findByProjectId(_projectIds);
            //获取项目的所有阶段
            List<CrmProjectStageRelate> relates = crmProjectStageRelateDao.findByProjectId(_projectIds);
            //获取项目的委托人和对方当事人
            List<CrmProjectClient> crmProjectClients = crmProjectClientDao.getClientByProjectIds(_projectIds);
            //每个项目id对应一个项目基本信息
            Map<String, CrmProject> projectMap = projects.isEmpty() ? new HashMap<>() : projects.stream().collect(Collectors.toMap(CrmProject::getRecid, p -> p));
            //每个项目id对应一个项目团队成员
            Map<String, List<CrmProjectMember>> projectMemberMap = members.isEmpty() ? new HashMap<>() : members.stream().collect(Collectors.groupingBy(CrmProjectMember::getProjectid));
            //每个项目id对应一组项目阶段
            Map<String, List<CrmProjectStageRelate>> relateMap = relates.isEmpty() ? new HashMap<>(0) : relates.stream().collect(Collectors.groupingBy(CrmProjectStageRelate::getProjectid));
            //每个项目id对应一个委托人和对方当事人
            Map<String, CrmProjectClient> clientMap = crmProjectClients.isEmpty() ? new HashMap<>(0) : crmProjectClients.stream().collect(Collectors.toMap(CrmProjectClient::getProjectid, Function.identity(), (c1, c2) -> c1));
            //分组项目下的聊天组
            for (String id : _projectIds) {
                //根据项目id分组聊天组
                List<CrmChatGroupExt> _tmpGroups = _projectGroups.stream().filter(x -> x.getObjectid().equals(id)).collect(Collectors.toList());
                //获取当前项目的基本信息
                CrmProject _currentProject = projectMap.get(id);
                String _projectName = "";
                //如果当前项目有基本信息则获取项目名称,如果没有则跳过下面操作,继续下一次循环
                if (_currentProject != null) {
                    _projectName = _currentProject.getRecname();
                } else {
                    continue;
                }

                //项目是否完成
                if (_currentProject.getProjectstatus() == BaseEnum.ProjectStatusType.Complete.getProjectStatusType()) {
                    _isComplete = true;
                } else {
                    _isComplete = false;
                }


                //获取项目成员
                List<CrmProjectMember> _members = projectMemberMap.get(id);

                //获取当前项目包含的阶段
                List<Integer> _tmpStages = new ArrayList<>();
                for (CrmChatGroupExt _g : _tmpGroups) {
                    int _stageid = _g.getProjectstageid();
                    boolean _isExists = _tmpStages.contains(_stageid);
                    //没有添加过则添加阶段id
                    if (!_isExists) {
                        _tmpStages.add(_stageid);
                    }
                }

                //获取委托方信息
                String _titleid = "";
                CrmProjectClient _client = clientMap.get(id);
                if (_client != null) {
                    _titleid = _client.getTitleid();
                }

                //根据项目id获取阶段信息
                List<CrmProjectStageRelate> _projectStageRelations = relateMap.get(id);

                //阶段聊天组
                for (Integer _sid : _tmpStages) {
                    ChatGroupListResponse _stageGroupResponse = new ChatGroupListResponse();
                    //项目名称
                    _stageGroupResponse.setProjectname(_projectName);
                    //项目id
                    _stageGroupResponse.setProjectid(id);
                    //阶段id
                    _stageGroupResponse.setStageid(_sid);
                    //根据阶段id获取阶段信息
                    List<CrmProjectStageRelate> _theStageRelations = _projectStageRelations.stream().filter(x -> x.getStageid() == _sid).collect(Collectors.toList());
                    //获取阶段状态
                    int _theProjectStageStatus = _theStageRelations.get(0).getStagestatus();
                    //获取阶段创建时间
                    Date _completedTime = _theStageRelations.get(0).getCompletedtime();
                    Date _createTime = _theStageRelations.get(0).getCreatetime();
                    //格式化阶段创建时间
                    String _strBeginTime = _dataFormat24Hour.format(_completedTime);
                    //阶段创建时间
                    _stageGroupResponse.setCompletedtime(_strBeginTime);
                    //判断当前阶段是否完成, 1开始,2完成
                    if (_theProjectStageStatus == 2) {
                        _isComplete = true;
                        // _stageGroupResponse.setCompletedtime(_completedTime);
                    } else {
                        _isComplete = false;
                        // _stageGroupResponse.setCompletedtime(_createTime);
                    }

                    _stageGroupResponse.setInlawyermember(false);
                    //当前用户是否是律师团队成员
                    if (_members != null && _members.size() > 0) {
                        long _laywerMemberCount = _members.stream().filter(x -> x.getStageid() == _sid
                                && x.getGrouptype() == BaseEnum.GroupType.Lawyer.getGroupType()
                                && x.getUserid() == userid).count();
                        if (_laywerMemberCount > 0) {
                            _stageGroupResponse.setInlawyermember(true);
                        }
                    }

                    //设置阶段名称
                    CrmProjectStage _currentStage = getProjectStage(_sid);
                    String _stageName = "";
                    if (_currentStage != null) {
                        _stageName = _currentStage.getStagename();
                    }
                    _stageGroupResponse.setStagename(_stageName);


                    List<ChatGroupListItem> _groups = new ArrayList<>();
                    //在聊天组中获取当前阶段的聊天组
                    List<CrmChatGroupExt> _tmpStageGroups = _tmpGroups.stream().filter(x -> x.getProjectstageid() == _sid).collect(Collectors.toList());
                    //在任务中获取当前项目当前阶段的任务
                    List<CrmProjectTask> _tmpProjectTask = _projectTask.stream().filter(x -> x.getProjectid().equals(id) && x.getStageid() == _sid).collect(Collectors.toList());

                    if (_tmpProjectTask != null) {
                        for (CrmProjectTask t : _tmpProjectTask) {
                            //当前任务,当前阶段的聊天组
                            List<CrmChatGroupExt> _tmpGroup = _taskGroups.stream().filter(x -> x.getObjectid().equals(t.getTaskid()) && x.getProjectstageid() == _sid).collect(Collectors.toList());

                            for (CrmChatGroupExt _g : _tmpGroup) {
                                //获取任务的基本信息
                                List<CrmTask> _theTaskInfo = getTaskInfo(_g.getObjectid());
                                SimpleDateFormat _dataFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

                                String _taskContent = "";
                                String _taskBeinTime = "";
                                if (_theTaskInfo != null && _theTaskInfo.size() > 0) {
                                    _taskContent = _theTaskInfo.get(0).getContent();
                                    // if (_theTaskInfo.get(0).getBegintime() != null) {
                                    _taskBeinTime = _dataFormat.format(_theTaskInfo.get(0).getBegintime());
                                    // }
                                }
                                //任务内容
                                _g.setGroupname(_taskContent);
                                //任务开始时间
                                _g.setTaskbegintime(_taskBeinTime);
                            }

                            _tmpStageGroups.addAll(_tmpGroup);
                        }
                    }

                    //获取聊天组中最后的聊天信息
                    List<Date> _theStageLatestChatTime = new ArrayList<>();
                    for (CrmChatGroupExt _g : _tmpStageGroups) {
                        //当前阶段未完成
                        if (_theProjectStageStatus != 2) {
                            //获取最后的聊天内容
                            List<CrmChatMessage> _messages = getChatMessageLatest(_g.getGroupid());
                            if (_messages.size() > 0) {
                                Date _theCreateTime = _messages.get(0).getCreatetime();
                                _theStageLatestChatTime.add(_theCreateTime);
                            }
                        }
                        //项目类型
                        int _theProjectType = 0;
                        //项目的基本信息
                        CrmProject _theProject = getProject(_g.getObjectid());
                        if (_theProject != null) {
                            _theProjectType = _theProject.getProjecttype();
                        }

                        ChatGroupListItem _item = new ChatGroupListItem();
                        //聊天组id
                        _item.setGroupid(_g.getGroupid());
                        //任务开始时间
                        if (_g.getTaskbegintime() != null && !_g.getTaskbegintime().isEmpty()) {
                            _item.setBegintime(_g.getTaskbegintime());
                        }
                        //聊天组名称
                        _item.setGroupname(_g.getGroupname());
                        //
                        _item.setObjectid(_g.getObjectid());
                        //聊天组对象类型 0 项目, 1任务
                        _item.setChattype(_g.getChattype());
                        //阶段id
                        _item.setStageid(_g.getProjectstageid());
                        _item.setGrouptype(_g.getGrouptype());
                        _item.setChatcontent("");
                        _item.setLatestchattime("");
                        _item.setChatnumber(0);
                        //  _item.setBegintime("");
                        //当前阶段id
                        _item.setCurrentchatstageid(_g.getStageid());
                        //是否完成
                        _item.setIscomplete(_isComplete);
                        //项目类型
                        _item.setProjecttype(_theProjectType);
                        //聊天组是否可以发送消息
                        _item.setIsavtive(_g.getIsactive());

                        //ext data
                        _item.setContent(_g.getContent());
                        _item.setMsgtime(_g.getMsgtime());
                        _item.setRelcount(_g.getRelcount());
                        _item.setMsgseq(_g.getMsgseq());
                        _item.setMsgcontent(_g.getMsgcontent());

                        //聊天阶段没有推进
                        String _currentChatStageName = "委协";
                        if (_item.getCurrentchatstageid() > 0) {
                            List<CrmChatStage> _theChatStage = getChatStages_new(_item.getCurrentchatstageid());
                            if (_theChatStage != null && _theChatStage.size() > 0) {
                                //阶段名称
                                _currentChatStageName = _theChatStage.get(0).getStagename();
                            }
                        } else {
                            if (_titleid != null && !_titleid.isEmpty()) {
                                List<CrmChatStage> _chatStages = getChatStages(_currentProject.getProjecttype(), _g.getGroupid(), _sid, _titleid);
                                if (_g.getStageid() == 0) {
                                    if (_chatStages != null && _chatStages.size() > 0) {
                                        CrmChatStage _currentChatStage = _chatStages.get(0);
                                        _item.setCurrentchatstageid(_currentChatStage.getStageid());
                                        // _currentChatStageName= _currentChatStage.getStagename();
                                    }
                                }

                                //设置聊天阶段名字
                                Optional<CrmChatStage> _theChatStage = _chatStages.stream().filter(x -> x.getStageid() == _item.getCurrentchatstageid()).findFirst();
                                if (_theChatStage.isPresent()) {
                                    if (!_theChatStage.get().getStagename().isEmpty()) {
                                        _currentChatStageName = _theChatStage.get().getStagename();
                                    }
                                } else {
                                    ChatStageGetRequest _chatStageRequest = new ChatStageGetRequest();
                                    _chatStageRequest.setGroupid(_item.getGroupid());
                                    _chatStageRequest.setStageid(_item.getStageid());

                                    List<ChatStage> _theChatStages = getChatStage(_chatStageRequest, userid);
                                    if (_theChatStages != null && _theChatStages.size() > 0) {

                                        if (_theChatStages.get(0).getStagename() != null) {
                                            _currentChatStageName = _theChatStages.get(0).getStagename();
                                        }
                                    }
                                }
                            } else {
                                if (_item.getGrouptype() == 3) {
                                    _currentChatStageName = "律协";
                                }
                            }
                        }
                        _item.setCurrentchatstagename(_currentChatStageName);
                        _groups.add(_item);
                    }

                    if (_theStageLatestChatTime.size() > 0) {
                        Date _maxDate = _theStageLatestChatTime.stream().max(Date::compareTo).get();
                        String _maxDateString = _dataFormat24Hour.format(_maxDate);
                        _stageGroupResponse.setCompletedtime(_maxDateString);
                        if (null == _maxDateString) {
                            continue;
                        }
                    }

                    _stageGroupResponse.setGroups(_groups);
                    _listResponse.add(_stageGroupResponse);
                }
            }


//            任务聊天组
            for (CrmChatGroupExt _g : _taskGroups) {

                long _theCount = _projectTask.stream().filter(x -> x.getStageid() == _g.getProjectstageid() && x.getTaskid().equals(_g.getObjectid())).count();
                if (_theCount > 0) {
                    //  continue;
                }

                ChatGroupListResponse _stageGroupResponse = new ChatGroupListResponse();
                _stageGroupResponse.setProjectname("");
                _stageGroupResponse.setProjectid("");
                _stageGroupResponse.setStagename("");
                _stageGroupResponse.setStageid(_g.getStageid());


                String _beginTime = "";
                String _taskContent = "";
                List<CrmTask> _taskList = getTaskInfo(_g.getObjectid());
                if (_taskList != null && _taskList.size() > 0) {
                    CrmTask _currentTask = _taskList.get(0);
                    Date _currentBeginTime = _currentTask.getBegintime();
                    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    _beginTime = df.format(_currentBeginTime);
                    if (_currentTask.getTaskstatus() == BaseEnum.TaskStatus.Complete.getTaskStatus()) {
                        _isComplete = true;
                    } else {
                        _isComplete = false;

                    }
                    _taskContent = _currentTask.getContent();
                    Date _taskCompleteTime = _currentTask.getCompletetime();
                    _stageGroupResponse.setCompletedtime(_dataFormat24Hour.format(_taskCompleteTime));

                    //if not complete use latest chat message time
                    if (!_isComplete) {
                        List<CrmChatMessage> _chatMessages = getChatMessageLatest(_g.getGroupid());
                        if (_chatMessages != null && _chatMessages.size() > 0) {
                            Date _latestChatTime = _chatMessages.get(0).getCreatetime();
                            _stageGroupResponse.setCompletedtime(_dataFormat24Hour.format(_latestChatTime));
                        }
                    }
                }

                List<ChatGroupListItem> _groups = new ArrayList<>();
                ChatGroupListItem _item = new ChatGroupListItem();
                _item.setProjectid("");
                _item.setGroupid(_g.getGroupid());
                _item.setGroupname(_taskContent);
                _item.setObjectid(_g.getObjectid());
                _item.setChattype(_g.getChattype());
                _item.setStageid(_g.getStageid());
                _item.setGrouptype(_g.getGrouptype());
                _item.setChatcontent("");
                _item.setLatestchattime("");
                _item.setChatnumber(0);
                _item.setBegintime(_beginTime);
                //任务是否已经完成
                _item.setIscomplete(_isComplete);
                _item.setCurrentchatstagename("委托");
                _item.setIsavtive(_g.getIsactive());

                //ext data
                _item.setContent(_g.getContent());
                _item.setMsgtime(_g.getMsgtime());
                _item.setRelcount(_g.getRelcount());
                _item.setMsgseq(_g.getMsgseq());
                _item.setMsgcontent(_g.getMsgcontent());


                //任务没有项目类型
                _item.setProjecttype(0);
                _groups.add(_item);
                _stageGroupResponse.setGroups(_groups);
                _stageGroupResponse.setInlawyermember(true);
                _listResponse.add(_stageGroupResponse);
            }
            List<ChatGroupListResponse> noTimeList = _listResponse.stream().filter(c -> c.getCompletedtime() == null).collect(Collectors.toList());
            List<ChatGroupListResponse> completeList = _listResponse.stream().filter(c -> c.getCompletedtime() != null).sorted(Comparator.comparing(ChatGroupListResponse::getCompletedtime, Comparator.reverseOrder())).collect(Collectors.toList());
            completeList.addAll(noTimeList);
            pageData.setDatalist(completeList);

        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return pageData;
    }

    //获取聊天阶段
    public List<ChatStage> getChatStage(ChatStageGetRequest _request, int uerid) {
        List<ChatStage> _stages = new ArrayList<>();
        try {

            String _groupid = _request.getGroupid();
            int _stageid = _request.getStageid();


            int _theDataStageid = _stageid;
            CrmProjectStage _theProjectSate = getProjectStage(_stageid);
            if (_theProjectSate != null && _theProjectSate.getDatastageid() > 0) {
                _theDataStageid = _theProjectSate.getDatastageid();
            }

            CrmChatGroup _group = getChatGroup2(_groupid);
            String _projectid = _group.getObjectid();

            //获取关联的委托人
            String _clientid = _group.getRelatedid();
            if (_clientid.isEmpty()) {
                List<CrmProjectClient> _theClients = getProjectClients(_projectid, _stageid);
                if (_theClients != null && _theClients.size() > 0) {
                    Optional<CrmProjectClient> _client = _theClients.stream().filter(x -> x.getPersontype() == BaseEnum.PersonType.Client.getPersonType()).findFirst();
                    if (_client.isPresent()) {

                        //_clientid = _client.getRecid();
                        _clientid = _client.get().getRecid();
                    }

                }
            }


            //get client title id ,by clientid
            CrmProjectClient _client = getProjectClient2(_clientid);

            String _titleid = "";
            if (_client != null) {
                _titleid = _client.getTitleid();
            }

            //get datatitleid
            String _finalTitleid = _titleid;
            CrmProjectTitle _currentTitle = getProjectTitleInfo2(_titleid);
            if (_currentTitle != null && _currentTitle.getDatatitleid() != null && !_currentTitle.getDatatitleid().isEmpty()) {
                _finalTitleid = _currentTitle.getDatatitleid();
            }

            CrmProject _project = getProject(_projectid);
            if (_project != null) {
                int _projectType = _project.getProjecttype();
                List<CrmChatStage> _stageList = getChatStages(_projectType, _groupid, _theDataStageid, _finalTitleid);

                for (CrmChatStage _stage : _stageList) {
                    ChatStage stage = toChatStage(_stage);
                    _stages.add(stage);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return _stages;
    }

    //推进到下一个阶段
    public ChatGroupDetailResponse toNextChatSate(ChatNextStageRequest _request, int userid) {
        int result = 0;
        int finalResult = 0;
        ChatGroupDetailResponse _detailResponse = new ChatGroupDetailResponse();
        try {
            String _groupid = _request.getGroupid();
            int _currentStageid = _request.getCurrentstageid();
            int _nextStageid = _request.getNextstageid();

            CrmChatGroup _theChatGroup = getChatGroup2(_groupid);
            String _theProjectId = _theChatGroup.getObjectid();
            int _theStageId = _theChatGroup.getProjectstageid();

            List<CrmChatGroup> _groups = getChatGroup3(_theProjectId, _theStageId);
            for (CrmChatGroup g : _groups) {

                //获取需要删除的数据
                List<CrmChatStageHistory> oldTopics = getCurrentChatTopics(g.getGroupid(), _currentStageid);
                //删除老数据
                if (oldTopics.size() > 0) {
                    deleteTopics(oldTopics);
                }

                //写入新数据
                saveTopics(g.getGroupid(), _currentStageid, userid, _request.getTopics());

                //更新聊天组的当前阶段
                finalResult = updateGroupStage(g.getGroupid(), _nextStageid);
            }

            //获取下一个阶段的数据
            List<CrmChatTopicHistoryList> _nextTopics = getTopicHistory(_groupid, _nextStageid);
            if (_nextTopics.size() == 0) {
                _nextTopics = getTopicBasic(_nextStageid);
            }

            List<ChatTopic> _topicList = new ArrayList<>();
            for (CrmChatTopicHistoryList _topic : _nextTopics) {
                _topicList.add(toChatTopic(_topic));
            }

            CrmChatGroup _chatGroup = getChatGroup(_groupid);
            String _groupName = _chatGroup.getGroupname();

            _detailResponse.setTopics(_topicList);
            _detailResponse.setGroupname(_groupName);
            _detailResponse.setGroupid(_request.getGroupid());
            _detailResponse.setStageid(_nextStageid);

        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }

        return _detailResponse;
    }

    //保存当前阶段的主题
    @Transactional
    public int saveGroupTopicHistory(ChatCheckTopicRequest _request, int userid) {
        int result = 0;
        int finalResult = 0;
        try {

            //BigInteger _projectid = _request.getProjectid();
            String _groupid = _request.getGroupid();
            int _stageid = _request.getStageid();

            CrmChatGroup _theChatGroup = getChatGroup2(_groupid);
            String _theProjectId = _theChatGroup.getObjectid();
            int _theStageId = _theChatGroup.getProjectstageid();

            List<CrmChatGroup> _groups = getChatGroup3(_theProjectId, _theStageId);
            for (CrmChatGroup g : _groups) {

                //获取需要删除的数据
                List<CrmChatStageHistory> oldTopics = getCurrentChatTopics(g.getGroupid(), _stageid);

                //删除老数据
                if (oldTopics.size() > 0) {
                    deleteTopics(oldTopics);
                }

                //写入新数据
                finalResult = saveTopics(g.getGroupid(), _stageid, userid, _request.getTopics());
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return finalResult;
    }

    //获取聊天组详情
    public ChatGroupDetailResponse getChatGroupDetail(ChatGroupDetailRequest _request) {
        ChatGroupDetailResponse _response = new ChatGroupDetailResponse();
        _response.setIsactive(false);

        try {
            String _groupid = _request.getGroupid();
            CrmChatGroup _group = getChatGroup(_groupid);
            int _currentStageid = 0;
            if (_group != null) {
                _currentStageid = _group.getStageid();
                _response.setIsactive(_group.getIsactive());
            }

            //主题历史
            List<CrmChatTopicHistoryList> _topics = getTopicHistory(_groupid, _currentStageid);

            //如果还没有历史
            if (_topics.size() == 0) {
                _topics = getTopicBasic(_currentStageid);
            }

            List<ChatTopic> _topicList = new ArrayList<>();
            for (CrmChatTopicHistoryList _topic : _topics) {
                _topicList.add(toChatTopic(_topic));
            }

            _response.setTopics(_topicList);
            _response.setGroupname(_group.getGroupname());
            _response.setGroupid(_request.getGroupid());
            _response.setStageid(_currentStageid);

        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return _response;
    }

    //发送聊天消息
    @Transactional
    public int ChatSendMessage(ChatSendRequest _request, int userid) {
        int result = 0;
        try {
            String _groupid = _request.getGroupid();
            int _stageid = _request.getStageid();

            String _strSql = " insert into crm_chat_message(recid,groupid,stageid,content,reccreator)" +
                    " values(:recid,:groupid,:stageid,:content,:reccreator); ";

            Map<String, Object> _params = new HashMap<>();
            _params.put("recid", UUID.randomUUID().toString());
            _params.put("groupid", _groupid);
            _params.put("stageid", _stageid);
            _params.put("content", _request.getMessage());
            _params.put("reccreator", userid);
            result = updateEntity(_strSql, _params);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return result;
    }

    public List<ChatMessage> getChatMessage(ChatGetMessageRequest _requst, int uesrid) {
        List<ChatMessage> _messageList = new ArrayList<>();

        try {
            //获取需要删除的数据
            String _strSql = " select m.recid,m.groupid,m.stageid,m.content,m.reccreator,m.createtime,u.username as reccreator_name  " +
                    " from crm_chat_message m " +
                    " inner join crm_user u on m.reccreator=u.id " +
                    " where groupid=:groupid ";

            Map<String, Object> _params = new HashMap<>();
            _params.put("groupid", _requst.getGroupid());
            List<CrmChatMessage> _messages = getEntitys(_strSql, _params, CrmChatMessage.class);
            for (CrmChatMessage _message : _messages) {
                ChatMessage _chat = toChatMessage(_message);
                _messageList.add(_chat);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return _messageList;
    }

    //搜索聊天组
    public List<ChatGroup> searchChat(ChatSearchRequest _request, int userid) {
        List<ChatGroup> _final = new ArrayList<>();
        String _searchKey = _request.getSearchkey();
        try {

            String _strSql = " select recid,recname,objectid,chattype,stageid,prefixname,grouptype,relatedid,isactive, createtime " +
                    " from crm_chat_group " +
                    " where recstatus=1 and recname like   " + " '%" + _searchKey + "%' " +
                    " and recid in (select groupid from crm_project_member where userid=:userid     " +
                    "                union  select groupid from crm_task_member where userid=:userid )" +
                    "  order by createtime desc";

            Map<String, Object> _params = new HashMap<>();
            _params.put("userid", userid);
            List<CrmChatGroup> _list = getEntitys(_strSql, _params, CrmChatGroup.class);
            for (CrmChatGroup _g : _list) {
                _final.add(toChatGroup(_g));
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return _final;
    }

    private ChatGroup toChatGroup(CrmChatGroup group) {
        ChatGroup _group = new ChatGroup();
        _group.setObjectid(group.getObjectid());
        _group.setGroupid(group.getGroupid());
        _group.setGroupname(group.getPrefixname() + group.getGroupname());
        _group.setChattype(group.getChattype());
        _group.setStageid(group.getProjectstageid());
        _group.setGrouptype(group.getGrouptype());
        return _group;
    }

    public ChatStage toChatStage(CrmChatStage stage) {
        ChatStage _stage = new ChatStage();
        _stage.setStageid(stage.getStageid());
        _stage.setStagename(stage.getStagename());
        _stage.setStagetype(stage.getStagetype());
        if (stage.getBranchstatus() > 0) {
            _stage.setBranchstatus(BaseEnum.ChatBranchStageOperateType.Start.getChatBranchStageOperateType());
        }
        return _stage;
    }

    private ChatTopic toChatTopic(CrmChatTopicHistoryList topic) {
        ChatTopic _topic = new ChatTopic();
        _topic.setTopicid(topic.getTopicid());
        _topic.setTopicname(topic.getTopicname());
        _topic.setStageid(topic.getStageid());
        _topic.setIscomplete(topic.iscomplete());
        _topic.setConfig(topic.getConfig());
        return _topic;
    }

    //更新聊天组的当前阶段id
    private int updateGroupStage(String groupid, int stageid) {
        String _strSql = " update crm_chat_group set chatstageid=:chatstageid where recid=:recid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("chatstageid", stageid);
        _params.put("recid", groupid);
        int result = updateEntity(_strSql, _params);
        return result;
    }
    //更新聊天组为不可用
    public int updateGroupRecstatus(String groupid) {
        String _strSql = " update crm_chat_group set recstatus=0 where recid=:recid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", groupid);
        int result = updateEntity(_strSql, _params);
        return result;
    }

    private List<CrmChatStageHistory> getCurrentChatTopics(String groupid, int stageid) {

        //获取需要删除的数据
        String strSelectSql = " select recid, groupid,stageid,topicid,iscomplete,reccreator" +
                " from crm_chat_stage_history " +
                " where groupid=:groupid " +
                " and stageid=:stageid ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("groupid", groupid);
        _params.put("stageid", stageid);
        List<CrmChatStageHistory> oldTopics = getEntitys(strSelectSql, _params, CrmChatStageHistory.class);
        return oldTopics;
    }

    private int deleteTopics(List<CrmChatStageHistory> oldTopics) {
        int result = 0;
        int finalResult = 0;

        String strDeleteSql = " delete from crm_chat_stage_history where recid=:recid ";
        Map<String, Object> _params = new HashMap<>();
        for (CrmChatStageHistory _t : oldTopics) {
            _params.clear();
            _params.put("recid", _t.getRecid());
            result = updateEntity(strDeleteSql, _params);
            finalResult = finalResult + result;
        }
        return finalResult;
    }

    private int saveTopics(String groupid, int stageid, int userid, List<ChatCheckTopicItem> _topics) {

        int result = 0;
        int finalResult = 0;

        String strInsertSql = " insert into crm_chat_stage_history(recid,groupid,stageid,topicid,iscomplete,reccreator) " +
                " values(:recid,:groupid,:stageid,:topicid,:iscomplete,:reccreator) ";

        Map<String, Object> _params = new HashMap<>();

        for (ChatCheckTopicItem _topic : _topics) {

            _params.clear();
            _params.put("recid", UUID.randomUUID().toString());
            _params.put("groupid", groupid);
            _params.put("stageid", stageid);
            _params.put("topicid", _topic.getTopicid());
            _params.put("iscomplete", _topic.ischecked());
            _params.put("reccreator", userid);
            result = updateEntity(strInsertSql, _params);
            finalResult = finalResult + result;
        }
        return finalResult;
    }

    private List<CrmChatTopicHistoryList> getTopicHistory(String groupid, int stageid) {
        List<CrmChatTopicHistoryList> _topics = new ArrayList<>();
        try {
            //主题历史
            String _strSql = " select h.recid,t.recname,h.stageid,h.iscomplete,h.topicid, t.config from crm_chat_stage_history h " +
                    " inner join crm_chat_topic t on h.topicid=t.recid " +
                    " where t.recstatus=1 and h.groupid=:groupid" +
                    " and h.stageid=:stageid " +
                    " order by h.topicid asc ";
            Map<String, Object> _params = new HashMap<>();
            _params.put("groupid", groupid);
            _params.put("stageid", stageid);
            _topics = getEntitys(_strSql, _params, CrmChatTopicHistoryList.class);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return _topics;
    }

    private List<CrmChatTopicHistoryList> getTopicBasic(int stageid) {
        //主题历史
        String _strSql = " select uuid() as recid, t.recid as topicid,t.recname,t.stageid, false as iscomplete, t.config" +
                " from crm_chat_topic t " +
                " where t.recstatus=1 and t.stageid=:stageid " +
                " order by t.recorder asc ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("stageid", stageid);
        List<CrmChatTopicHistoryList> _topics = getEntitys(_strSql, _params, CrmChatTopicHistoryList.class);
        return _topics;
    }

    private ChatMessage toChatMessage(CrmChatMessage message) {

        ChatMessage _message = new ChatMessage();
        _message.setMessageid(message.getMessageid());
        _message.setGroupid(message.getGroupid());
        _message.setStageid(message.getStageid());
        _message.setContent(message.getContent());
        _message.setReccreator(message.getReccreator());
        _message.setCreatetime(message.getCreatetime());
        _message.setReccreatorname(message.getReccreatorname());
        return _message;
    }

    private List<CrmChatGroup> getGroupListByObjectid(String objectid) {
        String _strSql = "select color,recid,recname,objectid,chattype,chatstageid,prefixname,stageid,grouptype,relatedid,''as taskbegintime,isactive,createtime" +
                " from crm_chat_group " +
                " where objectid = :objectid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("objectid", objectid);
        List<CrmChatGroup> _list = getEntitys(_strSql, _params, CrmChatGroup.class);
        return _list;
    }

    private CrmChatGroup getChatGroup(String groupid) {
        CrmChatGroup _group = new CrmChatGroup();
        String _strSql = "select color,recid,recname,objectid,chattype,chatstageid,prefixname,stageid,grouptype,relatedid,''as taskbegintime,isactive,createtime" +
                " from crm_chat_group " +
                " where recid=:recid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", groupid);
        List<CrmChatGroup> _list = getEntitys(_strSql, _params, CrmChatGroup.class);
        if (_list != null && _list.size() > 0) {
            _group = _list.get(0);
        }
        return _group;
    }

    public CrmProject getProject(String projectid) {
        String strSql = " select * from crm_project where recid=:recid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", projectid);
        List<CrmProject> _list = getEntitys(strSql, _params, CrmProject.class);
        if (_list != null && _list.size() > 0) {
            return _list.get(0);
        } else {
            return null;
        }
    }

    public CrmTask getTask(String taskid) {
        String strSql = " select * from crm_task where recid=:recid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", taskid);
        List<CrmTask> _list = getEntitys(strSql, _params, CrmTask.class);
        if (_list != null && _list.size() > 0) {
            return _list.get(0);
        } else {
            return null;
        }
    }

    public List<CrmProject> getProjects(String projectIds) {
        String strSql = " select * from crm_project where recid in (" + projectIds + ")";
        List<CrmProject> _list = getEntitys(strSql, null, CrmProject.class);
        if (_list != null && _list.size() > 0) {
            return _list;
        } else {
            return null;
        }
    }

    public CrmProjectStage getProjectStage(int stageid) {

        String strSql = " select *,false as iscomplete from crm_project_stage where recid=:recid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", stageid);
        List<CrmProjectStage> _list = getEntitys(strSql, _params, CrmProjectStage.class);
        if (_list != null && _list.size() > 0) {
            return _list.get(0);
        } else {
            return null;
        }
    }

    public List<CrmTask> getTaskInfo(String taskid) {

        String _strSql = " select recid, recname,begintime,content,contenttype,address_name,address_location,taskstatus,completetime" +
                " from crm_task " +
                " where recid=:recid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", taskid);
        return getEntitys(_strSql, _params, CrmTask.class);
    }

    public ChatGroupDetailResponse getStageDetail(ChatStageDetailRequest _request, int userid) {
        int result = 0;
        int finalResult = 0;
        ChatGroupDetailResponse _detailResponse = new ChatGroupDetailResponse();
        try {

            String _groupid = _request.getGroupid();
            int _stageid = _request.getStageid();
            List<CrmChatTopicHistoryList> _nextTopics = getTopicHistory(_groupid, _stageid);
            if (_nextTopics.size() == 0) {
                _nextTopics = getTopicBasic(_stageid);
            }

            List<ChatTopic> _topicList = new ArrayList<>();
            for (CrmChatTopicHistoryList _topic : _nextTopics) {
                _topicList.add(toChatTopic(_topic));
            }

            CrmChatGroup _chatGroup = getChatGroup(_groupid);
            String _groupName = _chatGroup.getGroupname();

            _detailResponse.setTopics(_topicList);
            _detailResponse.setGroupname(_groupName);
            _detailResponse.setGroupid(_request.getGroupid());
            _detailResponse.setStageid(_stageid);

        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return _detailResponse;
    }

    public List<CrmProjectClient> getProjectClients(String projectid, int stageid) {

        String strClientSql = " select recid,stageid,rectype,title,name,contact,phone,mail,persontype,titleid,projectid " +
                " from crm_project_client " +
                " where  projectid=:projectid  and stageid=:stageid";

        Map<String, Object> _params = new HashMap<>();
        _params.put("projectid", projectid);
        _params.put("stageid", stageid);
        return getEntitys(strClientSql, _params, CrmProjectClient.class);
    }

    public List<CrmChatStage> getChatStages(int projectType, String groupid, int stageid, String titleid) {

        String _stageSql = " select s.recid,s.recname,s.stagetype,coalesce(h.chatstageid,0) branchstatus " +
                " from crm_chat_stage s " +
                " left join (select * from crm_chat_branch_stage_history where groupid=:groupid) h on s.recid=h.chatstageid " +
                " where s.recstatus=1 " +
                " and s.projecttype=:projecttype" +
                " and s.stageid=:stageid " +
                " and s.titleid=:titleid " +
                " order by s.recorder asc ";


        Map<String, Object> _params = new HashMap<>();
        _params.put("groupid", groupid);
        _params.put("projecttype", projectType);
        _params.put("stageid", stageid);
        _params.put("titleid", titleid);
        List<CrmChatStage> _stageList = getEntitys(_stageSql, _params, CrmChatStage.class);
        return _stageList;

    }

    private CrmChatGroup getChatGroup2(String groupid) {

        CrmChatGroup _group = new CrmChatGroup();
        String strSql = "select color,recid,recname,objectid,chattype,chatstageid,prefixname,stageid,grouptype,relatedid,'' as taskbegintime,isactive,createtime" +
                " from crm_chat_group " +
                " where recid=:recid ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", groupid);
        List<CrmChatGroup> _list = getEntitys(strSql, _params, CrmChatGroup.class);
        if (_list != null && _list.size() > 0) {
            _group = _list.get(0);
        }
        return _group;
    }

    private CrmProjectClient getProjectClient2(String recid) {

        String strClientSql = " select recid,stageid,rectype,title,name,contact,phone,mail,persontype,titleid,projectid " +
                " from crm_project_client " +
                " where  recid=:recid ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", recid);
        List<CrmProjectClient> _clientList = getEntitys(strClientSql, _params, CrmProjectClient.class);
        if (_clientList != null && _clientList.size() > 0) {
            return _clientList.get(0);
        } else {
            return null;
        }
    }

    private CrmProjectTitle getProjectTitleInfo2(String titleid) {

        String strClientSql = " select recid,recname,datatitleid " +
                " from crm_project_title " +
                " where  recid=:recid ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", titleid);

        List<CrmProjectTitle> _titleList = getEntitys(strClientSql, _params, CrmProjectTitle.class);
        if (_titleList != null && _titleList.size() > 0) {
            return _titleList.get(0);
        } else {
            return null;
        }

    }

    @Transactional
    public int operateChatBranchStage(ChatBranchStageRequest _request, int operateType, int uerid) {

        int _result = 0;
        String _strSql = "";
        Map<String, Object> _params = new HashMap<>();


        String _theGroupId = _request.getGroupid();
        int _theChatStageId = _request.getChatstageid();
        CrmChatGroup _theChatGroup = getChatGroup(_theGroupId);
        if (_theChatGroup != null) {
            String _theObjectId = _theChatGroup.getObjectid();
            int _theStageId = _theChatGroup.getProjectstageid();
            List<CrmChatGroup> _chatGroups = getChatGroup3(_theObjectId, _theStageId);
            for (CrmChatGroup c : _chatGroups) {

                String _currentGroupID = c.getGroupid();

                if (BaseEnum.ChatBranchStageOperateType.Start.getChatBranchStageOperateType() == operateType) {
                    _strSql = " insert into crm_chat_branch_stage_history(recid,groupid,chatstageid) values(:recid,:groupid,:chatstageid) ";

                    String _recid = UUID.randomUUID().toString();
                    _params.clear();
                    _params.put("recid", _recid);
                    _params.put("groupid", _currentGroupID);
                    _params.put("chatstageid", _theChatStageId);
                    _result = updateEntity(_strSql, _params);
                } else {

                    _params.clear();
                    _strSql = " select recid,groupid,chatstageid from crm_chat_branch_stage_history where groupid=:groupid and  chatstageid=:chatstageid ";
                    _params.put("groupid", _currentGroupID);
                    _params.put("chatstageid", _theChatStageId);
                    List<CrmChatBranchStageHistory> _list = getEntitys(_strSql, _params, CrmChatBranchStageHistory.class);
                    if (_list != null && _list.size() > 0) {
                        for (CrmChatBranchStageHistory s : _list) {
                            String _recid = s.getRecid();
                            _strSql = " delete from crm_chat_branch_stage_history where recid=:recid";
                            _params.clear();
                            _params.put("recid", _recid);
                            _result = updateEntity(_strSql, _params);
                        }
                    } else {
                        _result = 1;
                    }
                }
                //end of loop
            }
        }


        return _result;
    }

    private CrmProjectClient getProjectClients(String projectid) {

        String strClientSql = " select recid,stageid,projectid,rectype,title,name,contact,phone,mail,persontype,titleid " +
                " from crm_project_client " +
                " where  projectid=:projectid ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("projectid", projectid);
        List<CrmProjectClient> _clientList = getEntitys(strClientSql, _params, CrmProjectClient.class);
        if (_clientList != null && _clientList.size() > 0) {
            return _clientList.get(0);
        } else {
            return null;
        }
    }

    private List<CrmChatGroup> getChatGroup3(String projectId, int stageid) {

        CrmChatGroup _group = new CrmChatGroup();
        String strSql = "select color,recid,recname,objectid,chattype,chatstageid,prefixname,stageid,grouptype,relatedid,'' as taskbegintime,isactive,createtime" +
                " from crm_chat_group " +
                " where objectid=:objectid " +
                " and recstatus=1 " +
                "  and  stageid=:stageid";

        Map<String, Object> _params = new HashMap<>();
        _params.put("objectid", projectId);
        _params.put("stageid", stageid);
        List<CrmChatGroup> _list = getEntitys(strSql, _params, CrmChatGroup.class);
        return _list;
    }

    public List<CrmProjectTask> getProjectTask() {

        String _strSql =
                " select pt.recid,pt.projectid,pt.stageid,pt.taskid,  t.recname as taskname, t.content,t.begintime,t.taskstatus,t.reccreator from crm_project_task pt inner join crm_task t on pt.taskid=t.recid  where pt.recstatus=1";

        List<CrmProjectTask> _tasks = getEntitys(_strSql, null, CrmProjectTask.class);
        return _tasks;
    }


    public List<CrmQuestion> getQuestions() {

        String _strSql =
                " select recid,question from crm_question " +
                        " where recstatus=1 " +
                        " order by recorder asc ";

        List<CrmQuestion> _result = getEntitys(_strSql, null, CrmQuestion.class);
        return _result;
    }


    public List<CrmAnswer> getAansere(int questionid) {

        String _strSql =
                " select recid,answer " +
                        " from crm_answer " +
                        " where recstatus=1  and questionid=:questionid" +
                        " order by recorder asc ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("questionid", questionid);

        List<CrmAnswer> _result = getEntitys(_strSql, _params, CrmAnswer.class);
        return _result;

    }

    /*
    get chat message unser the group
     */
    public List<CrmChatMessage> getChatMessageLatest(String groupid) {

        //获取需要删除的数据
        String _strSql = " select m.recid,m.groupid,m.stageid,m.content,m.reccreator,m.createtime,u.username as reccreator_name  " +
                " from crm_chat_message m " +
                " inner join crm_user u on m.reccreator=u.id " +
                " where groupid=:groupid " +
                " order by createtime desc limit 1";

        Map<String, Object> _params = new HashMap<>();
        _params.put("groupid", groupid);
        List<CrmChatMessage> _result = getEntitys(_strSql, _params, CrmChatMessage.class);
        return _result;
    }


    //聊天组列表
    public ChatGroupListPageResponse getGroupList(int userid, ChatGroupListRequest _request) {
        String searchKey = _request.getSearchkey();

        ChatGroupListPageResponse pageData = new ChatGroupListPageResponse();
        List<ChatGroup> _final = new ArrayList<>();
        List<ChatGroupListResponse> _listResponse = new ArrayList<>();
        SimpleDateFormat _dataFormat24Hour = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        try {
            String _strCountSql = "";
            String strSql = " select recid,recname,objectid,chattype,COALESCE(chatstageid,0) as chatstageid ,prefixname,stageid,grouptype," +
                    "relatedid,'' as taskbegintime,isactive," +
                    "'' as content, 0 as  msgtime, 0 as relcount, 0 as msgseq, " +
                    " '' as msgcontent " +
                    " from crm_chat_group g " +
                    " where recstatus=1  " +
                    " and recid in (" +
                    "select groupid from crm_project_member pm inner join crm_project p on pm.projectid=p.recid  where pm.recstatus=1 and  pm.userid=:userid and  p.recname like '%" + searchKey + "%' " +
                    "  union  " +
                    "select groupid from crm_task_member tm inner join crm_task t on tm.taskid=t.recid  where tm.recstatus=1 and  tm.userid=:userid and t.content like '%" + searchKey + "%' ) " +
                    "  order by createtime desc ";
            if (_request.getPageindex() > 0) {
                int _pageindex = _request.getPageindex();
                int _pageSize = _request.getPagesize();
                int _offset = (_pageindex - 1) * _pageSize;

                String _where_name_sql_project = "";
                String _where_name_sql_task = "";
                if (!searchKey.isEmpty()) {
                    _where_name_sql_project = " and (m.content like " + "'%" + searchKey + "%')";
                    _where_name_sql_task = " and (m.content like " + "'%" + searchKey + "%')";
                }

                strSql = " with tmp_project as(" +
                        "select pm.groupid from crm_project p\n" +
                        "left join crm_project_member pm on p.recid = pm.projectid\n" +
                        "where p.recstatus = 1 and pm.recstatus = 1\n" +
                        "and (pm.userid = :userid)\n" +
                        "),\n" +
                        "tmp_task as(\n" +
                        "select tm.groupid from crm_task t\n" +
                        "left join crm_task_member tm on t.recid = tm.taskid\n" +
                        "where t.recstatus = 1 and tm.recstatus = 1\n" +
                        "and (tm.userid = :userid)\n" +
                        "),\n" +
                        "tmp_call as(\n" +
                        "select m.groupid, p.recname as groupname, u.realname as username, " +
                        " m.content, m.msgtime, m.msgseq,m.msgcontent  \n" +
                        "                    from crm_chat_callback_message m\n" +
                        "                    left join crm_chat_group g on m.groupid = g.recid\n" +
                        "                    left join crm_user u on m.reccreator = u.id\n" +
                        "                    inner join crm_project p on p.recid = g.objectid\n" +
                        "                    where m.recstatus = 1 and m.groupid in(\n" +
                        "                       select groupid from tmp_project\n" +
                        ")\n" +
                        _where_name_sql_project +
                        "                     union\n" +
                        "select m.groupid, p.content as groupname, u.realname as username, " +
                        " m.content, m.msgtime, m.msgseq,m.msgcontent \n" +
                        "                    from crm_chat_callback_message m\n" +
                        "                    left join crm_chat_group g on m.groupid = g.recid\n" +
                        "                    left join crm_user u on m.reccreator = u.id\n" +
                        "                    inner join crm_task p on p.recid = g.objectid\n" +
                        "                    where m.recstatus = 1 and m.groupid in(\n" +
                        "                       select groupid from tmp_task\n" +
                        ")\n" +
                        _where_name_sql_task +
                        ") \n" +
                        " select * from(\n" +
                        " select recid,recname,objectid,chattype,COALESCE(chatstageid,0) as chatstageid ,prefixname,stageid,grouptype," +
                        "relatedid,'' as taskbegintime,isactive,\n" +
                        " (select count(1) from tmp_call where  tmp_call.groupid = g.recid) as relcount, \n" +
                        " (select content from tmp_call where tmp_call.groupid = g.recid order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1 ) as content, \n" +
                        " (select msgtime from tmp_call where tmp_call.groupid = g.recid order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1 ) as msgtime, \n" +
                        " (select COALESCE(msgseq,0) as msgseq from tmp_call where tmp_call.groupid = g.recid order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1 ) as msgseq, \n" +
                        " (select msgcontent from tmp_call where tmp_call.groupid = g.recid order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1 ) as msgcontent \n" +
                        "                     from crm_chat_group g  \n" +
                        "                     where recstatus=1   \n" +
                        "                     and recid in ( \n" +
                        "select groupid from tmp_call" +
                        ")\n" +
                        ") t ";

                _strCountSql = String.format(" select count(1) from (%s) as t  ", strSql);
                strSql += "  order by msgtime desc, msgseq desc limit " + _pageSize + " offset " + _offset;
            }

            Map<String, Object> _params = new HashMap<>();
            _params.put("userid", userid);
            List<CrmChatGroupExt> _list = getEntitys(strSql, _params, CrmChatGroupExt.class);
            if (_list != null && _list.size() > 0 && !_strCountSql.isEmpty()) {
                int _count = getEntityCount(_strCountSql, _params);
                PageCountInfo page = new PageCountInfo();
                page.setPage(_request.getPageindex());
                page.setTotal(_count);
                pageData.setPage(page);
            }

            List<CrmChatGroupExt> _projectGroups = _list.stream().filter(x -> x.getObjectid() != null
                    && !x.getObjectid().isEmpty()
                    && x.getChattype() == BaseEnum.ChatType.Project.getChatType()).collect(Collectors.toList());

            List<CrmChatGroupExt> _taskGroups = _list.stream().filter(x -> x.getObjectid() != null
                    && !x.getObjectid().isEmpty()
                    && x.getChattype() == BaseEnum.ChatType.Task.getChatType()).collect(Collectors.toList());

            List<CrmProjectTask> _projectTask = getProjectTask();

            //获取项目id
            boolean _isComplete = false;
            List<String> _projectIds = new ArrayList<>();
            for (CrmChatGroupExt _g : _projectGroups) {
                String _projectid = _g.getObjectid();
                boolean _isExists = _projectIds.contains(_projectid);
                if (!_isExists) {
                    _projectIds.add(_projectid);
                }
            }


            for (String id : _projectIds) {
                List<CrmChatGroupExt> _tmpGroups = _projectGroups.stream().filter(x -> x.getObjectid().equals(id)).collect(Collectors.toList());
                CrmProject _currentProject = getProject(id);
                String _projectName = "";
                if (_currentProject != null) {
                    _projectName = _currentProject.getRecname();
                }

                //项目是否完成
                if (_currentProject.getProjectstatus() == BaseEnum.ProjectStatusType.Complete.getProjectStatusType()) {
                    _isComplete = true;
                } else {
                    _isComplete = false;
                }


                //项目成员
                List<CrmProjectMember> _members = _commonRepository.getProjectMember(id, userid);

                //阶段数据
                List<Integer> _tmpStages = new ArrayList<>();
                for (CrmChatGroupExt _g : _tmpGroups) {
                    int _stageid = _g.getProjectstageid();
                    boolean _isExists = _tmpStages.contains(_stageid);
                    if (!_isExists) {
                        _tmpStages.add(_stageid);
                    }
                }

                //get project client
                String _titleid = "";
                CrmProjectClient _client = getProjectClients(id);
                if (_client != null) {
                    _titleid = _client.getTitleid();
                }


                List<CrmProjectStageRelate> _projectStageRelations = _commonRepository.getProjectStageRelate(id);

                //阶段聊天组
                for (Integer _sid : _tmpStages) {
                    ChatGroupListResponse _stageGroupResponse = new ChatGroupListResponse();

                    _stageGroupResponse.setProjectname(_projectName);
                    _stageGroupResponse.setProjectid(id);
                    _stageGroupResponse.setStageid(_sid);

                    List<CrmProjectStageRelate> _theStageRelations = _projectStageRelations.stream().filter(x -> x.getStageid() == _sid).collect(Collectors.toList());
                    int _theProjectStageStatus = _theStageRelations.get(0).getStagestatus();
                    Date _completedTime = _theStageRelations.get(0).getCompletedtime();
                    Date _createTime = _theStageRelations.get(0).getCreatetime();
                    _stageGroupResponse.setCompletedtime(_dataFormat24Hour.format(_completedTime));

                    if (_theProjectStageStatus == 2) {
                        _isComplete = true;
                        // _stageGroupResponse.setCompletedtime(_completedTime);
                    } else {
                        _isComplete = false;
                        // _stageGroupResponse.setCompletedtime(_createTime);

                    }


                    _stageGroupResponse.setInlawyermember(false);
                    if (_members != null && _members.size() > 0) {
                        long _laywerMemberCount = _members.stream().filter(x -> x.getStageid() == _sid
                                && x.getGrouptype() == BaseEnum.GroupType.Lawyer.getGroupType()
                                && x.getUserid() == userid).count();
                        if (_laywerMemberCount > 0) {
                            _stageGroupResponse.setInlawyermember(true);
                        }
                    }

                    //设置阶段名称
                    CrmProjectStage _currentStage = getProjectStage(_sid);
                    String _stageName = "";
                    if (_currentStage != null) {
                        _stageName = _currentStage.getStagename();
                    }
                    _stageGroupResponse.setStagename(_stageName);


                    List<ChatGroupListItem> _groups = new ArrayList<>();
                    List<CrmChatGroupExt> _tmpStageGroups = _tmpGroups.stream().filter(x -> x.getProjectstageid() == _sid).collect(Collectors.toList());

                    List<CrmProjectTask> _tmpProjectTask = _tmpProjectTask = _projectTask.stream().filter(x -> x.getProjectid().equals(id) && x.getStageid() == _sid).collect(Collectors.toList());
                    if (_tmpProjectTask != null) {
                        for (CrmProjectTask t : _tmpProjectTask) {
                            List<CrmChatGroupExt> _tmpGroup = _taskGroups.stream().filter(x -> x.getObjectid().equals(t.getTaskid()) && x.getProjectstageid() == _sid).collect(Collectors.toList());

                            for (CrmChatGroupExt _g : _tmpGroup) {
                                List<CrmTask> _theTaskInfo = getTaskInfo(_g.getObjectid());
                                SimpleDateFormat _dataFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

                                String _taskContent = "";
                                String _taskBeinTime = "";
                                if (_theTaskInfo != null && _theTaskInfo.size() > 0) {
                                    _taskContent = _theTaskInfo.get(0).getContent();
                                    // if (_theTaskInfo.get(0).getBegintime() != null) {
                                    _taskBeinTime = _dataFormat.format(_theTaskInfo.get(0).getBegintime());
                                    // }
                                }
                                _g.setGroupname(_taskContent);
                                _g.setTaskbegintime(_taskBeinTime);
                            }

                            _tmpStageGroups.addAll(_tmpGroup);
                        }
                    }

                    List<Date> _theStageLatestChatTime = new ArrayList<>();
                    for (CrmChatGroupExt _g : _tmpStageGroups) {
                        //设置聊天阶段名字
                        String _currentChatStageName = "委协";

                        if (_theProjectStageStatus != 2) {
                            List<CrmChatMessage> _messages = getChatMessageLatest(_g.getGroupid());
                            if (_messages.size() > 0) {
                                Date _theCreateTime = _messages.get(0).getCreatetime();
                                _theStageLatestChatTime.add(_theCreateTime);
                            }
                        }

                        int _theProjectType = 0;
                        CrmProject _theProject = getProject(_g.getObjectid());
                        if (_theProject != null) {
                            _theProjectType = _theProject.getProjecttype();
                        }

                        ChatGroupListItem _item = new ChatGroupListItem();
                        _item.setGroupid(_g.getGroupid());
                        if (_g.getTaskbegintime() != null && !_g.getTaskbegintime().isEmpty()) {
                            _item.setBegintime(_g.getTaskbegintime());
                        }
                        _item.setGroupname(_g.getGroupname());
                        _item.setObjectid(_g.getObjectid());
                        _item.setChattype(_g.getChattype());
                        _item.setStageid(_g.getProjectstageid());
                        _item.setGrouptype(_g.getGrouptype());
                        _item.setChatcontent("");
                        _item.setLatestchattime("");
                        _item.setChatnumber(0);
                        //  _item.setBegintime("");
                        _item.setCurrentchatstageid(_g.getStageid());
                        _item.setIscomplete(_isComplete);
                        _item.setProjecttype(_theProjectType);
                        _item.setIsavtive(_g.getIsactive());

                        //聊天阶段没有推进
                        if (_titleid != null && !_titleid.isEmpty()) {
                            List<CrmChatStage> _chatStages = getChatStages(_currentProject.getProjecttype(), _g.getGroupid(), _sid, _titleid);
                            if (_g.getStageid() == 0) {
                                if (_chatStages != null && _chatStages.size() > 0) {
                                    CrmChatStage _currentChatStage = _chatStages.get(0);
                                    _item.setCurrentchatstageid(_currentChatStage.getStageid());
                                }
                            }

                            Optional<CrmChatStage> _theChatStage = _chatStages.stream().filter(x -> x.getStageid() == _item.getCurrentchatstageid()).findFirst();
                            if (_theChatStage.isPresent()) {
                                if (!_theChatStage.get().getStagename().isEmpty()) {
                                    _currentChatStageName = _theChatStage.get().getStagename();
                                }
                            } else {
                                ChatStageGetRequest _chatStageRequest = new ChatStageGetRequest();
                                _chatStageRequest.setGroupid(_item.getGroupid());
                                _chatStageRequest.setStageid(_item.getStageid());

                                List<ChatStage> _theChatStages = getChatStage(_chatStageRequest, userid);
                                if (_theChatStages != null && _theChatStages.size() > 0) {

                                    if (_theChatStages.get(0).getStagename() != null) {
                                        _currentChatStageName = _theChatStages.get(0).getStagename();

                                    }
                                }
                            }
                        } else {
                            if (_item.getGrouptype() == 3) {
                                _currentChatStageName = "律协";
                            }
                        }
                        _item.setCurrentchatstagename(_currentChatStageName);

                        //ext data
                        _item.setContent(_g.getContent());
                        _item.setMsgtime(_g.getMsgtime());
                        _item.setRelcount(_g.getRelcount());
                        _item.setMsgseq(_g.getMsgseq());
                        _item.setMsgcontent(_g.getMsgcontent());

                        _groups.add(_item);
                    }

                    if (_theStageLatestChatTime.size() > 0) {

                        Date _maxDate = _theStageLatestChatTime.stream().max(Date::compareTo).get();
                        _stageGroupResponse.setCompletedtime(_dataFormat24Hour.format(_maxDate));
                    }

                    _stageGroupResponse.setGroups(_groups);
                    _listResponse.add(_stageGroupResponse);
                }
            }


            //任务聊天组
            for (CrmChatGroupExt _g : _taskGroups) {

                long _theCount = _projectTask.stream().filter(x -> x.getStageid() == _g.getProjectstageid() && x.getTaskid().equals(_g.getObjectid())).count();
                if (_theCount > 0) {
                    //  continue;
                }

                ChatGroupListResponse _stageGroupResponse = new ChatGroupListResponse();
                _stageGroupResponse.setProjectname("");
                _stageGroupResponse.setProjectid("");
                _stageGroupResponse.setStagename("");
                _stageGroupResponse.setStageid(_g.getStageid());

                String _beginTime = "";
                String _taskContent = "";
                List<CrmTask> _taskList = getTaskInfo(_g.getObjectid());
                if (_taskList != null && _taskList.size() > 0) {
                    CrmTask _currentTask = _taskList.get(0);
                    Date _currentBeginTime = _currentTask.getBegintime();
                    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    _beginTime = df.format(_currentBeginTime);
                    if (_currentTask.getTaskstatus() == BaseEnum.TaskStatus.Complete.getTaskStatus()) {
                        _isComplete = true;
                    } else {
                        _isComplete = false;


                    }
                    _taskContent = _currentTask.getContent();
                    Date _taskCompleteTime = _currentTask.getCompletetime();
                    _stageGroupResponse.setCompletedtime(_dataFormat24Hour.format(_taskCompleteTime));

                    //if not complete use latest chat message time
                    if (!_isComplete) {
                        List<CrmChatMessage> _chatMessages = getChatMessageLatest(_g.getGroupid());
                        if (_chatMessages != null && _chatMessages.size() > 0) {
                            Date _latestChatTime = _chatMessages.get(0).getCreatetime();
                            _stageGroupResponse.setCompletedtime(_dataFormat24Hour.format(_latestChatTime));
                        }
                    }

                }

                List<ChatGroupListItem> _groups = new ArrayList<>();
                ChatGroupListItem _item = new ChatGroupListItem();
                _item.setProjectid("");
                _item.setGroupid(_g.getGroupid());
                _item.setGroupname(_taskContent);
                _item.setObjectid(_g.getObjectid());
                _item.setChattype(_g.getChattype());
                _item.setStageid(_g.getStageid());
                _item.setGrouptype(_g.getGrouptype());
                _item.setChatcontent("");
                _item.setLatestchattime("");
                _item.setChatnumber(0);
                _item.setBegintime(_beginTime);
                //任务是否已经完成
                _item.setIscomplete(_isComplete);
                _item.setCurrentchatstagename("委托");
                _item.setIsavtive(_g.getIsactive());

                //任务没有项目类型
                _item.setProjecttype(0);

                //ext data
                _item.setContent(_g.getContent());
                _item.setMsgtime(_g.getMsgtime());
                _item.setRelcount(_g.getRelcount());
                _item.setMsgseq(_g.getMsgseq());
                _item.setMsgcontent(_g.getMsgcontent());

                _groups.add(_item);
                _stageGroupResponse.setGroups(_groups);
                _stageGroupResponse.setInlawyermember(true);
                _listResponse.add(_stageGroupResponse);
            }
            _listResponse.sort(Comparator.comparing(ChatGroupListResponse::getCompletedtime).reversed());

            pageData.setDataList(_listResponse);
            //Comparator.reverseOrder()
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return pageData;
    }


    public List<CrmChatStage> getChatStages_new(int chatStageid) {
        String _stageSql = " select s.recid,s.recname,s.stagetype,0 branchstatus " +
                " from crm_chat_stage s " +
                " where s.recstatus=1 " +
                " and s.recid=:stageid ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("stageid", chatStageid);
        List<CrmChatStage> _stageList = getEntitys(_stageSql, _params, CrmChatStage.class);
        return _stageList;
    }

    @Transactional
    public int ChatCallBackMessage(ChatCallBackMessage _request, int userid) {
        int result = 0;
        try {
            //保存回调信息
            String _strSql = " insert into crm_chat_callback_message(recid,groupid,content,recstatus,reccreator, " +
                    " createtime,sender,msgtime,callbacktype,device, " +
                    " msgtype,random,msgseq,msgcategory,msgcontent) " +
                    " values(:recid,:groupid,:content,:recstatus,:reccreator," +
                    " :createtime,:sender,:msgtime,:callbacktype,:device," +
                    " :msgtype,:random,:msgseq,:msgcategory,:msgcontent); ";

            Map<String, Object> _params = new HashMap<>();
            _params.put("recid", UUID.randomUUID().toString());
            _params.put("groupid", _request.getGroupid());
            _params.put("content", _request.getContent());
            _params.put("recstatus", _request.getRecstatus());
            _params.put("reccreator", _request.getReccreator());

            _params.put("createtime", _request.getCreatetime());
            _params.put("sender", _request.getSender());
            _params.put("msgtime", _request.getMsgtime());
            _params.put("callbacktype", _request.getCallbacktype());
            _params.put("device", _request.getDevice());

            _params.put("msgtype", _request.getMsgtype());
            _params.put("random", _request.getRandom());
            _params.put("msgseq", _request.getMsgseq());
            _params.put("msgcategory", _request.getMsgcategory());
            _params.put("msgcontent", _request.getMsgcontent());
            result = updateEntity(_strSql, _params);
            //更新聊天组创建时间用以排序
            updateGroupCreateTime(_request.getGroupid(), _request.getCreatetime());
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return result;
    }

    private int updateGroupCreateTime(String groupid, Date time) {
        String strSql = "SELECT color,recid,recname,objectid,chattype,chatstageid,prefixname,stageid,grouptype,relatedid,''as taskbegintime,isactive,createtime FROM crm_chat_group"
                + "  where recid = :recid ";
        Map<String, Object> params = new HashMap<>();
        params.put("recid", groupid);
        List<CrmChatGroup> crmChatGroup = getEntitys(strSql, params, CrmChatGroup.class);

        String _strSql = " update crm_chat_group set createtime = :createTime where objectid = :objectid and stageid = :stageid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("objectid", crmChatGroup.get(0).getObjectid());
        _params.put("stageid", crmChatGroup.get(0).getProjectstageid());
        _params.put("createTime", time);
        int result = updateEntity(_strSql, _params);
        return result;
    }

    public List<GlobalSearchDetailListResponse> searchDetailListMessage(GlobalSearchDetailListRequest _requst, int uesrid) {
        List<GlobalSearchDetailListResponse> _messageList = new ArrayList<>();
        try {
            String _where_name_sql = "";
            if (!_requst.getSearchkey().isEmpty()) {
                _where_name_sql = " and m.content like " + "'%" + _requst.getSearchkey() + "%'";
            }

            String _strSql = " select  m.recid, m.groupid, g.recname as groupname, m.sender as senderid,  u.realname as sendername, u.headimage, " +
                    "m.content, m.msgtime, COALESCE(m.msgseq,0) as msgseq, m.msgcontent \n" +
                    "from crm_chat_callback_message m \n" +
                    "left join crm_chat_group g on m.groupid = g.recid \n" +
                    "left join crm_user u on m.sender = u.id \n" +
                    "where m.groupid = :groupid ";

            _strSql += _where_name_sql;
            _strSql += " order by m.msgtime desc,m.msgseq desc ";
            Map<String, Object> _params = new HashMap<>();
            _params.put("groupid", _requst.getGroupid());

            _messageList = getEntitys(_strSql, _params, GlobalSearchDetailListResponse.class);

        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return _messageList;
    }

    public List<GlobalSearchDetailResponse> searchDetailMessage(GlobalSearchDetailRequest _requst, int uesrid) {
        List<GlobalSearchDetailResponse> _messageList = new ArrayList<>();
        try {
            String _strSql = " select  m.recid, m.groupid, g.recname as groupname, m.sender as senderid,  u.realname as sendername, u.headimage, " +
                    "m.content, m.msgtime, COALESCE(m.msgseq,0) as msgseq, msgcontent \n" +
                    "from crm_chat_callback_message m \n" +
                    "left join crm_chat_group g on m.groupid = g.recid \n" +
                    "left join crm_user u on m.sender = u.id \n" +
                    "where m.groupid = :groupid ";

            if (_requst.getMsgseqbegin() > 0 && _requst.getMsgseqend() > 0) {
                _strSql += " and m.msgseq >= " + _requst.getMsgseqbegin();
                _strSql += " and m.msgseq < " + _requst.getMsgseqend();
                _strSql += " order by m.msgtime asc, m.msgseq asc";
            } else if (_requst.getMsgseq() > 0) {
                String _where_left_sql = "";
                String _where_right_sql = "";
                _where_left_sql = " and m.msgseq <= " + _requst.getMsgseq();
                _where_right_sql = " and m.msgseq >= " + _requst.getMsgseq();
                _strSql = "(" + _strSql + _where_left_sql + " order by m.msgtime asc, m.msgseq asc limit 10)"
                        + " union ("
                        + _strSql + _where_right_sql + " order by m.msgtime asc, m.msgseq asc limit 10)";
            } else {
                _strSql += " and 1=2";
            }

            Map<String, Object> _params = new HashMap<>();
            _params.put("groupid", _requst.getGroupid());

            _messageList = getEntitys(_strSql, _params, GlobalSearchDetailResponse.class);

        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return _messageList;
    }
}
